package db2;

import beans2.Patient;

import java.sql.*;

public class SignDao extends Connbean{
    private Connection connection=null;
    public boolean  addSign(Patient patient,int schid){//添加患者
        PreparedStatement pstmt=null;
        int indid=0;
        int result1=0;
        int result2=0;
        String sql1="insert into YY_indent(pid, schid, date, state) values(?,?,?,?)";
        String sql2="select * from YY_indent";
        String sql3="insert into YY_sign(pid, indid, state, number) values(?,?,?,?)";
        if(patient==null)
            return false;
        boolean result=false;
        try{
            System.out.println("SignDao addSign : sql1 began");
            connection=getConnection();
            pstmt=connection.prepareStatement(sql1);//生成订单表
            pstmt.setString(1,patient.getAccount());
            pstmt.setInt(2,schid);
            pstmt.setDate(3, new Date(System.currentTimeMillis()));
            pstmt.setString(4,"待就医");
            result1=pstmt.executeUpdate();
            System.out.println("SignDao addSign : sql1 over");

            System.out.println("SignDao addSign : sql2 began");
            pstmt=connection.prepareStatement(sql2);//找到订单id
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                indid++;
            }
            System.out.println("SignDao addSign : sql2 over");
            System.out.println("indid:"+indid);
            System.out.println("SignDao addSign : sql3 began");
            pstmt=connection.prepareStatement(sql3);//插入签到表
            pstmt.setString(1,patient.getAccount());
            pstmt.setInt(2,indid);
            pstmt.setString(3,"已签到");
            pstmt.setInt(4,patient.getOrder());
            result2=pstmt.executeUpdate();
            System.out.println("SignDao addSign : sql3 over");
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        result=result1>0&&result2>0&&indid!=0?true:false;
        return result;
    }

    //就诊完成更新
    public boolean UpSign(String Account,int indid){
        //就诊完成更新
        //修改签到表和订单表
        //将签到表的state修改成已完成就诊
        //并且将number的数值减一
        PreparedStatement preparedStatement=null;
        PreparedStatement preparedStatement1=null;
        int result1=0;
        int result2=0;
        int result3=0;

        try {
            connection=getConnection();


            //修改YY_sign表的排序
            //sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )"
            //sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )";
            String sql1="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id=? ) and YY_sign.state=(select YY_sign.state state from YY_Indent ,YY_sign where YY_indent.id=YY_sign.indid and YY_Indent.id=?))";
            preparedStatement1=connection.prepareStatement(sql1);
            preparedStatement1.setInt(1,indid);
            preparedStatement1.setInt(2,indid);
            preparedStatement1.setInt(3,indid);
            result3=preparedStatement1.executeUpdate();

            String sql="update YY_Sign set state='完成就诊' where pid=? and indid=?";//修改YY_sign表状态
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,Account);
            preparedStatement.setInt(2,indid);
            result1=preparedStatement.executeUpdate();

            sql="update YY_indent set state='完成就医' where id=?";//修改YY_indent状态
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,indid);
            result2=preparedStatement.executeUpdate();

            System.out.println("排序更新成功");
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result1>0&&result2>0&&result3>0?true:false;
    }
    public boolean SignLate(String Account,int indid){//就诊更新-过号

        PreparedStatement preparedStatement=null;
        int result1=0;
        int result2=0;
        int result3=0;
        int Late_num=0;
        System.out.println("SignDao SignLate");
        try {
            connection=getConnection();
            String sql;


            sql="update YY_indent set state='已过号' where id=?";//修改YY_indent状态
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,indid);
            result2=preparedStatement.executeUpdate();

            //修改YY_sign表的排序
            //sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )"
            sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )";

            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,indid);
            preparedStatement.setInt(2,indid);
            result3=preparedStatement.executeUpdate();

            String sql_num_late="select * from YY_sign where state='已过号'";//过号排序
            preparedStatement=connection.prepareStatement(sql_num_late);
            ResultSet rs=preparedStatement.executeQuery();
            while(rs.next()){
                Late_num++;
            }
            Late_num++;
            sql="update YY_Sign set state='已过号' ,number=? where pid=? and indid=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,Late_num);
            preparedStatement.setString(2,Account);
            preparedStatement.setInt(3,indid);
            result1=preparedStatement.executeUpdate();
//            String sql_num_late="select * from YY_sign where state='已过号'";//过号排序
//            preparedStatement=connection.prepareStatement(sql_num_late);
//            ResultSet rs=preparedStatement.executeQuery();
//            while(rs.next()){
//                Late_num++;
//            }
//            Late_num++;
//            String sql="update YY_Sign set number=?, state='已过号' where pid=? and indid=?";//修改YY_sign的state和number
//            preparedStatement=connection.prepareStatement(sql);
//            preparedStatement.setInt(1,Late_num);
//            preparedStatement.setString(2,Account);
//            preparedStatement.setInt(3,indid);
//
//            result1=preparedStatement.executeUpdate();
//            sql="update YY_indent set state='已过号' where id=?";//修改YY_indent状态
//            preparedStatement=connection.prepareStatement(sql);
//            preparedStatement.setInt(1,indid);
//            result2=preparedStatement.executeUpdate();
//            //修改YY_sign已签到患者排序表的排序
//            //update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )
//            sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id= ? ) and YY_sign.state='已签到' )";
//            sql="update  YY_sign set number=number-1 where YY_sign.id in(select YY_sign.id  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING,YY_patient  where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and YY_sign.pid=YY_patient.account and docid=( select docid  from YY_sign ,YY_Indent ,YY_DOCTOR_SCHEDULING where YY_Sign.indid=YY_Indent .id and YY_Indent.schid=YY_DOCTOR_SCHEDULING.id and  YY_Indent .id=?) and YY_sign.number>(select number from YY_sign,YY_indent where YY_sign.indid=YY_indent.id  and YY_indent.id=? ) and YY_sign.state=(select YY_sign.state state from YY_Indent ,YY_sign where YY_indent.id=YY_sign.indid and YY_Indent.id=?))";
//            preparedStatement=connection.prepareStatement(sql);
//            preparedStatement.setInt(1,indid);
//            preparedStatement.setInt(2,indid);
//            preparedStatement.setInt(3,indid);
////            result3=preparedStatement.executeUpdate();
//            preparedStatement.close();
//            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result1>0&&result2>0&&result3>0?true:false;
    }

    public int SignSum(String DoctorID){//今日预约总数
        PreparedStatement preparedStatement=null;
        ResultSet result=null;
        int sum=0;

        try {
            connection=getConnection();

            String sql="select * from YY_indent,YY_DOCTOR_SCHEDULING where YY_DOCTOR_SCHEDULING.id=YY_indent.SCHID and YY_DOCTOR_SCHEDULING .docid=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,DoctorID);
            result=preparedStatement.executeQuery();
            while(result.next()){
                sum++;
            }
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return sum;
    }

    public int SignTreatSum(String DoctorID){//治疗完成总量
        PreparedStatement preparedStatement=null;
        ResultSet result=null;
        int sum=0;

        try {
            connection=getConnection();

            String sql="select * from YY_sign,YY_indent ,YY_DOCTOR_SCHEDULING where YY_sign.indid=YY_indent .id and YY_indent .schid=YY_DOCTOR_SCHEDULING .id and YY_DOCTOR_SCHEDULING.docid=? and YY_sign.state='完成就诊'";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,DoctorID);
            result=preparedStatement.executeQuery();
            while(result.next()){
                sum++;
            }
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return sum;
    }
}
